﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using TT.Module.sysLogin;
using System.Data;
using SfcorpDLL.ety;
using GT.ORM;

namespace SfcorpDLL.dao
{
    public class daoAccountDMT
    {
        etyBaeSystemParamter basSysParam;

        public daoAccountDMT(etyBaeSystemParamter ieyBasClassDeclare)
        {
            basSysParam = ieyBasClassDeclare;
        }
        
        //取得总表
        public DataTable GetDmtCustomFblSum(etyDmtCustomFblSummary inEty)
        {
//            string sql = @"select distinct s.CLIENT_COD,s.CLIENT_NAME,s.MONTH_ID,disp_datetime(s.BILL_CONFIRM_DT) BILL_CONFIRM_DT,disp_date(s.RECORD_DT) RECORD_DT,s.WORK_WAY_NAM,s.SERVICE_NO,s.PREBAL_ID,s.BILL_NO,s.ORIGINAL_BILL_NO,s.CARGO_NAM,s.C_SHIP_NAM,s.VOYAGE,TO_PORT_TIM,s.LEAV_PORT_TIM,s.CONTRACT_NO,s.I_E_NAM,s.TRANS_COUNTER,S20E_COUNTER,S20F_COUNTER,S40E_COUNTER,S40F_COUNTER,S45E_COUNTER,S45F_COUNTER,SOTHERE_COUNTER,SOTHERF_COUNTER,DANG_COUNTER,FEE_SUMMNY,FEE_SHIP_WORK,FEE_SHIP_ANCHOR,FEE_FRUNK_WRAPIG,FEE_SET_SEAL,FEE_WORK_COST,FEE_CNTR_E_CY,FEE_CNTR_F_CY,FEE_CARGO_CY,FEE_OTHER,FEE_RETURN,RE_MARK,s.GKEY,disp_datetime(s.DOWNLOAD_TIM )DOWNLOAD_TIM 
//                ,MATCH_CONTRACT_ID,AVG_SIM_PERCENT1,AVG_SIM_PERCENT2,jcontract_no,SEA_BILL_NO,AGENT_BILL_NO ";
//            sql += getMmtCustomFblSum_SubString2;
//            return basSysParam.SqlHelp.ExecuteReader(sql, inEty);
            string sql = @"select distinct s.CLIENT_COD,s.CLIENT_NAME,s.MONTH_ID,disp_datetime(s.BILL_CONFIRM_DT) BILL_CONFIRM_DT,disp_date(s.RECORD_DT) RECORD_DT,s.WORK_WAY_NAM,s.SERVICE_NO,s.PREBAL_ID,s.BILL_NO,s.ORIGINAL_BILL_NO,s.CARGO_NAM,s.C_SHIP_NAM,s.VOYAGE,TO_PORT_TIM,s.LEAV_PORT_TIM,s.CONTRACT_NO,s.I_E_NAM,s.TRANS_COUNTER,S20E_COUNTER,S20F_COUNTER,S40E_COUNTER,S40F_COUNTER,S45E_COUNTER,S45F_COUNTER,SOTHERE_COUNTER,SOTHERF_COUNTER,DANG_COUNTER,FEE_SUMMNY,FEE_SHIP_WORK,FEE_SHIP_ANCHOR,FEE_FRUNK_WRAPIG,FEE_SET_SEAL,FEE_WORK_COST,FEE_CNTR_E_CY,FEE_CNTR_F_CY,FEE_CARGO_CY,FEE_OTHER,FEE_RETURN,RE_MARK,s.GKEY,disp_datetime(s.DOWNLOAD_TIM )DOWNLOAD_TIM 
                ,MATCH_FEE_AMOUNT_ID,MATCH_CONTRACT_ID,AVG_SIM_PERCENT1,AVG_SIM_PERCENT2,jcontract_no,SEA_BILL_NO,AGENT_BILL_NO,'' CNTRS,'' LOC_AMOUNT
                from GZP_DMT_CUSTOM_FBL_SUMMARY s
                left join MATCH_GZP_DMT_CUSTOM_FBL_LUMP m 
                on s.bill_no=m.bill_no and s.original_bill_no=m.original_bill_no 
                where S.Month_Id='2015/05' ";
            return basSysParam.SqlHelp.ExecuteReader(sql, inEty);
        }

        //取得本系统
        public DataTable GetDmtCustomLocSum(etyDmtCustomFblSummary inEty)
        {
            string sql = @"select c.sea_bill_no,c.agent_bill_no,c.contract_no,count(distinct c.fabc_gkey) CNTRS,{0},sum(amount) LOC_AMOUNT ";
            sql += getMmtCustomFblSum_SubString;
            sql += " group by c.sea_bill_no,c.agent_bill_no,c.contract_no ";
            sql = string.Format(sql, getCrossSql(inEty));
            return basSysParam.SqlHelp.ExecuteReader(sql, inEty);
        }

        //取得交叉语句
        private string getCrossSql(etyDmtCustomFblSummary inEty)
        {
            string sql =
            @"select wm_concat(distinct item_desc) ";
            sql += getMmtCustomFblSum_SubString;
            DataTable dt = basSysParam.SqlHelp.ExecuteReader(sql, inEty);
            sql = dt.Rows[0][0].ToString();
            sql = TT.Common.dataProcessor.CrossString(sql, "item_desc", "amount");
            return sql;
        }

        //公共子语句
        private string getMmtCustomFblSum_SubString
        {
            get
            {
                string sql = @"
                from frt_agy_bns_con c 
                inner join vw_bill_result d on c.fabc_gkey=d.bill_key
                where c.contract_no in(
                                select distinct m.jcontract_no
                                from GZP_DMT_CUSTOM_FBL_SUMMARY s
                                left join MATCH_GZP_DMT_CUSTOM_FBL_LUMP m 
                                on s.bill_no=m.bill_no and s.original_bill_no=m.original_bill_no 
                                where s.Month_Id='2015/05'
                ) 
                and d.io_flag=0";
                return sql;
            }
        }

        public DataTable GetBillResult(etyChargeDtl inEty)
        {
            string sql = "select * from VW_BILL_RESULT where contract_no=:CHRG_FLT20 and io_flag=0 ";
            return basSysParam.SqlHelp.ExecuteReader(sql, inEty);
        }
    }
}